package com.supsi.segretaria.server;

import java.sql.*;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;

/**
 * Gestice la communicazione con DB ed esporre i metodi per interazione con le tabelle usando SQL.
 * User: ognjen
 * Date: 20.04.12
 */
public class DBServer {
    private static DBServer instance = null;
    private Connection conn;

    /*
        private constructor - connects to SQLite
     */
    private DBServer() {
        try {
            connect();
            //createSampleData();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    private void connect() throws Exception {
        Class.forName("org.sqlite.JDBC");
        conn = DriverManager.getConnection("jdbc:sqlite:resource/segreteria.db");
    }

    /*
        Default table structure with sample data.
     */
    public void createSampleData() throws SQLException {
        Statement stat = conn.createStatement();
        stat.executeUpdate("DROP TABLE IF EXISTS  messages");

        stat.executeUpdate("CREATE TABLE messages (id INTEGER PRIMARY KEY, userFrom TEXT, userTo TEXT, message TEXT, date TEXT, read INTEGER)");

        PreparedStatement prep = conn.prepareStatement("INSERT INTO messages values(?,?,?,?,?,?)");
        prep.setNull(1, Types.INTEGER);
        prep.setString(2, "100");
        prep.setString(3, "101");
        prep.setString(4, "Hello there 100");
        prep.setString(5, Calendar.getInstance().getTime().toString());
        prep.setInt(6, 0);
        prep.addBatch();

        prep.setNull(1, Types.INTEGER);
        prep.setString(2, "100");
        prep.setString(3, "101");
        prep.setString(4, "Another message 100");
        prep.setString(5, Calendar.getInstance().getTime().toString());
        prep.setInt(6, 0);
        prep.addBatch();

        prep.setNull(1, Types.INTEGER);
        prep.setString(2, "101");
        prep.setString(3, "Oghi");
        prep.setString(4,"You got new message");
        prep.setString(5, Calendar.getInstance().getTime().toString());
        prep.setInt(6,0);
        prep.addBatch();

        prep.setNull(1, Types.INTEGER);
        prep.setString(2, "102");
        prep.setString(3, "Oghi");
        prep.setString(4, "Is there a cat?");
        prep.setString(5, Calendar.getInstance().getTime().toString());
        prep.setInt(6,0);
        prep.addBatch();

        conn.setAutoCommit(false);
        prep.executeBatch();
        conn.setAutoCommit(true);
    }

    /*
        Save new message
        @param username
        @param message
        @return true / false depending on success of operation
     */
    public boolean insertMessage(String fromUser, String toUser, String message) {
        try {
            PreparedStatement prepared = conn.prepareStatement("INSERT INTO messages values(?,?,?,?,?,?)");
            prepared.setNull(1, Types.INTEGER);
            prepared.setString(2, fromUser);
            prepared.setString(3, toUser);
            prepared.setString(4, message);
            prepared.setString(5, Calendar.getInstance().getTime().toString());
            prepared.setInt(6,0);
            prepared.execute();
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
        return true;
    }

    /*
        Read all user messages
        @param username
     */
    public List<Message> getMessages(String username){
        List<Message> messages = new ArrayList<Message>();
        Boolean unread;
        try {
            if (!conn.isClosed()){
                Statement stat = conn.createStatement();
                ResultSet rs = stat.executeQuery("SELECT * FROM messages WHERE userTo = '" + username + "'");
                while (rs.next()) {
                    unread = rs.getInt(6) == 0;
                    Message msg = new Message( rs.getInt(1), rs.getString(2), rs.getString(3), rs.getString(5),rs.getString(4), unread );
                    messages.add( msg );
                }
            } else {
                System.out.println("Connection closed");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return messages;
    }

    public int getMessagesCount(String username){
        int numOfRows = 0;
        try {
            if (!conn.isClosed()){
                Statement stat = conn.createStatement();
                ResultSet resultSet = stat.executeQuery("SELECT count(*) FROM messages WHERE userTo = '" + username + "'");
                numOfRows = resultSet.getInt(1);
                resultSet.close();
            } else {
                System.out.println("Connection closed");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return numOfRows;
    }

    /*
        Cancella singolo messaggio selezionato con messages.id
     */
    public boolean delMessage(int msgId){
        try {
            if (!conn.isClosed()){
                Statement stat = conn.createStatement();
                stat.executeUpdate("DELETE FROM messages WHERE id =" + Integer.toString(msgId));
            } else {
                System.out.println("Connection closed");
                return false;
            }
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
        return true;
    }

    /*
        Cancella tutti i messaggi di un utente
     */

    public boolean deleteAllMessages(String user) {
        try {
            if (!conn.isClosed()){
                Statement stat = conn.createStatement();
                stat.executeUpdate("DELETE FROM messages WHERE userTo = '" + user + "'" );
            } else {
                System.out.println("Connection closed");
                return false;
            }
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
        return true;
    }

    /**
     * change message status from unread to read
     * @param msgId
     * @return operation successful
     */
    public boolean makeMessageRead(int msgId) {
        try {
            if (!conn.isClosed()){
                Statement stat = conn.createStatement();
                stat.executeUpdate("UPDATE messages SET read = 1 WHERE id = " + msgId );
            } else {
                System.out.println("Connection closed");
                return false;
            }
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
        return true;
    }

    /*
        Close connection
     */
    private void closeConnection() {
        try {
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    
    /*
        Visto che dobbiamo usare Singleton sulla DB, questo metodo torna l'instanca dell DB.
     */
    public static DBServer getInstance() {
        if (instance == null) {
            instance = new DBServer();
        }
        return instance;
    }
}
